<?php

namespace liketp\database;

use liketp\database\concern\WhereBuilder;
use liketp\database\exception\DatabaseException;
use liketp\exception\Exception;

class Builder
{
    use WhereBuilder;

    protected $sql = [
        'insert' => '%INSERT%%EXTRA% INTO %TABLE% (%FIELD%) VALUES (%DATA%) %COMMENT%',

        'insertAll' => '%INSERT%%EXTRA% INTO %TABLE% (%FIELD%) %DATA% %COMMENT%',

        'update' => 'UPDATE%EXTRA% %TABLE% SET %SET%%JOIN%%WHERE%%ORDER%%LIMIT%%LOCK%%COMMENT%',

        'delete' => 'DELETE%EXTRA% FROM %TABLE%%USING%%JOIN%%WHERE%%ORDER%%LIMIT%%LOCK%%COMMENT%',

        'select' => 'SELECT%DISTINCT%%EXTRA% %FIELD% FROM %TABLE%%FORCE%%JOIN%%WHERE%%GROUP%%HAVING%%UNION%%ORDER%%LIMIT%%LOCK%%COMMENT%',
    ];

    public function select($query)
    {
        return $this->parseSqlPartment($query, 'select');
    }

    public function insert($query)
    {
        return $this->parseSqlPartment($query, 'insert');
    }

    public function insertAll($query)
    {
        return $this->parseSqlPartment($query, 'insertAll');
    }

    public function update($query)
    {
        return $this->parseSqlPartment($query, 'update');
    }

    public function delete($query)
    {
        return $this->parseSqlPartment($query, 'delete');
    }

    public function buildSql(string $sql, array $data)
    {
        foreach ($data as $k => $v) {
            $sql = str_replace($k, '"' . $v . '"', $sql);
        }
        return $sql;
    }

    public function buildSelectSql($query)
    {
        $sql = $this->select($query);
        $data = $query->getBindData();
        $buildedSql = $this->buildSql($sql, $data);
        return '(' . $buildedSql . ')';
    }


    //------------------------------------------------ protected
    protected function backquote(string $name, ...$args): string
    {
        if (empty($name)) return '';
        $items = explode('.', $name);
        $items = array_merge($items, $args);
        $items = array_filter($items, 'is_string');
        return '`' . implode('`.`', $items) . '`';
    }

    // %WHERE% -> $this->parseWhere($query, $method)
    protected function parseSqlPartment($query, $method)
    {
        $sql = $this->sql[$method];
        preg_match_all('/%([A-Z]+)%/', $sql, $match);
        $patternList = $replaceList = [];

        if (count($match[0])) {
            $patternList = $match[0];
            $parseFns = $match[1];

            array_map(function ($fn) use ($query, $method, &$replaceList) {
                $fn = 'parse' . ucfirst(strtolower($fn));

                if ($fn == 'parseWhere') {
                    $replaceList[] = $this->$fn($query);
                } else {
                    $replaceList[] = $this->$fn($query, $method);
                }
            }, $parseFns);
        }

        return str_replace($patternList, $replaceList, $sql);
    }


    //------------------------------------------------ parser
    protected function parseInsert($query)
    {
        return $query->getOptions('replace') ? 'REPLACE' : 'INSERT';
    }

    protected function parseDistinct($query)
    {
        $distinct = strtolower($query->getOptions('distinct', ''));
        if (in_array($distinct, ['all', 'distinct', 'distinctrow']) == false) {
            $distinct = '';
        }
        return $this->wrap($distinct);;
    }

    protected function parseExtra($query)
    {
        $extras = $query->getOptions('extra', []);
        return $this->wrap(implode(' ', $extras));
    }

    protected function parseField($query, $method)
    {
        if ($method == 'select') {
            $res = $query->getOptions('field');
            if (count($res) == 0) return '*';
        } else if (strstr($method, 'insert')) {
            $res = $this->getInsertFields($query, $method);
        }

        array_walk($res, function (&$field) {
            $field = $this->backquote($field);
        });

        return implode(', ', $res);
    }

    protected function parseTable($query)
    {
        $table = $query->getTableName();
        if (empty($table)) throw new DatabaseException('table is empty');
        return $this->backquote($table);
    }

    protected function parseUsing($query)
    {
        $using = $query->getOptions('using');
        if (empty($using) == false) {
            $using = $query->getOptions('prefix') . $using;
            return $this->wrap($this->backquote($using), true, 'USING %$%');
        } else {
            return '';
        }
    }

    protected function parseData($query, $method = 'insert')
    {
        $data = $query->getOptions('data');
        $allowFields = $this->getInsertFields($query, $method);

        if ($method == 'insert') {
            $keys = [];
            foreach ($data as $k => $v) {
                if (in_array($k, $allowFields) == false) continue;
                $key = $query->setBindData($k, $v);
                $keys[] = $key;
            }
            return $this->wrap(implode(', ', $keys));
        } else if ($method == 'insertAll') {

            $valuesList = [];
            $fieldsCount = count($allowFields);

            foreach ($data as $record) {
                $record = array_filter($record, function ($k) use ($allowFields) {
                    return in_array($k, $allowFields);
                }, ARRAY_FILTER_USE_KEY);

                if (count($record) != $fieldsCount) continue;

                $placeholders = [];
                array_walk($record, function ($v, $k) use ($query, &$placeholders) {
                    $placeholders[] = $query->setBindData($k, $v);
                });
                $valuesList[] = implode(',', $placeholders);
            }

            return $this->wrap(implode('),(', $valuesList), true, 'VALUES (%$%)');
        }
    }

    protected function parseSet($query)
    {
        $data = $query->getOptions('data');
        $allowFields = $this->getAllowedFields($query);

        $items = [];
        foreach ($data as $k => $v) {
            if (in_array($k, $allowFields) == false) continue;
            $k = $this->backquote($k);

            if ($v instanceof Raw) {
                $v = $v->content();
            } else {
                $v = $query->setBindData($k, $v);
            }
            array_push($items, $k . '=' . $v);
        }

        if (empty($items)) throw new Exception('nothing to update.');

        return $this->wrap(implode(', ', $items));
    }

    protected function parseForce($query)
    {
        $force = $query->getOptions('force', '');
        $type = 'force';
        if (substr($force, 0, 1) == '!') {
            $type = 'ignore';
            $force = substr($force, 1);
        }
        return $this->wrap($force, true, $type . '(%$%)');;
    }

    protected function parseJoin($query)
    {
        $joins = $query->getOptions('join', []);
        $items = [];
        foreach ($joins as $join) {
            $items = $this->parseJoinItem($join);
        }
        return $this->wrap(implode(' ', $items));
    }

    protected function parseJoinItem(array $join)
    {
        return 'left join table1 on table1.field1=table2.field2';
    }

    protected function parseGroup($query)
    {
        $group = $query->getOptions('group', '');
        return $this->wrap($group, true, 'group by %$%');
    }

    protected function parseHaving($query)
    {
        $havings = $query->getOptions('having');
        return $this->wrap(implode(' and ', $havings), true, 'having %$%');
    }

    protected function parseUnion($query)
    {
        $union = $query->getOptions('union', '');
        return $this->wrap($union, true, 'union %$%');
    }

    protected function parseOrder($query)
    {
        $orders = $query->getOptions('order', []);
        $items = [];
        foreach ($orders as $k => $v) {
            $items[] = $k . ' ' . $v;
        }
        return $this->wrap(implode(', ', $items), true, 'order by %$%');
    }

    protected function parseLimit($query, $method)
    {
        $limit = $query->getOptions('limit', []);
        if ($method == 'delete') array_shift($limit);
        return $this->wrap(implode(',', $limit), true, 'limit %$%');
    }

    protected function parseLock($query)
    {
        return $this->wrap($query->getOptions('lock', ''));
    }

    protected function parseComment($query)
    {
        return $this->wrap($query->getOptions('comment', ''), true, '/* %$% */');
    }


    //------------------------------------------------ private
    private function wrap(string $item, bool $trim = true, $template = '')
    {
        $item = $trim ? trim($item) : $item;
        if (empty($item)) {
            return '';
        } else {
            if ($template) $item = str_replace('%$%', $item, $template);
            return ' ' . $item;
        }
    }

    private function getInsertFields($query, $method)
    {
        $fields = $this->getAllowedFields($query);
        $data = $query->getOptions('data', []);
        if ($method == 'insertAll')  $data = $data[0] ?? [];

        $dataKeys = array_keys($data);
        $fields = array_intersect($dataKeys, $fields);

        return $fields;
    }

    private function getAllowedFields($query)
    {
        $fields = $query->getOptions('field', []);
        if (empty($fields)) {
            $tableInfo = $query->tableInfo();
            $fields = array_keys($tableInfo['fields']);
        }
        return $fields;
    }
}
